This Python Jupyter notebook can parse watercare bills and extract the usage data. It can be used to generate plots of the usage data.
You can download this notebook from https://github.com/neon-ninja/watercare/blob/main/parse.ipynb
You can download PDFs from https://myaccount.watercare.co.nz/bills-and-payments. Set your start date filter as far back as it'll go, and download all the PDFs into a folder called pdfs. Here's a JS snippet you can put in your browser console to save you clicking them all:
var links = document.querySelectorAll('button.flex.hover\\:bg-blue-100')
console.log(`Found ${links.length} links`)
var index = 0
setInterval(function() {
if (index >= links.length) {
return
}
console.log(index)
links[index].click()
index++
}, 250)
In [1]:
import pandas as pd # Tabular data
pd.options.plotting.backend = "plotly"
import plotly.express as px # Plotting
from glob import glob # Finding files
import pdftotext # PDF parsing
from tqdm.auto import tqdm # Progress bars
import re # Regular expressions
from datetime import datetime
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", None)
files = sorted(glob("pdfs/*.pdf"))
len(files)
Out[1]:
64
In [2]:
def read_PDF(filename):
with open(filename, "rb") as f:
pdf = pdftotext.PDF(f)
text = ""
for page_text in pdf:
text += page_text.strip()
return text
def parse_number(amount):
if amount.endswith(" cr"):
return -float(amount[:-3])
return float(amount)
def parse_text(text):
if "$500 gift card" in text:
# This breaks the dollar_amounts regex
text = text.replace("$ 50", "")
dates = re.findall(r'(\d{2} [A-Z][a-z]{2} \d{4})', text)
dollar_amounts = [parse_number(d) for d in re.findall(r'\$ ([\d\.]+(?: cr)?)', text)]
usage = [parse_number(d) for d in re.findall(r'([\d\.]+) kL', text)]
unit_rates = [parse_number(d) for d in re.findall(r'\$([\d\.]+)/kL', text)]
if len(unit_rates) == 4:
# Rate changed
unit_rates = unit_rates[::2]
consumption_period = re.search(r"Consumption period (\d+) days", text).group(1)
this_reading_match = re.search(r"This reading\s+(\d{2}-\w{3}-\d{2})\s+(\d+)\s*(Estimate|Actual)?", text)
this_reading_date = this_reading_match.group(1)
this_reading_date = datetime.strptime(this_reading_date, "%d-%b-%y")
this_reading_value = int(this_reading_match.group(2))
this_reading_type = this_reading_match.group(3)
return {
"Invoice date": dates[0],
"Due date": dates[1],
"Total due ($)": dollar_amounts[0],
"Opening balance ($)": dollar_amounts[1],
"Payments received ($)": dollar_amounts[2],
"Balance still owing ($)": dollar_amounts[3],
"Water consumption ($)": dollar_amounts[4],
"Wastewater consumption ($)": dollar_amounts[5],
"Wastewater fixed ($)": dollar_amounts[6],
"Balance of current charges ($)": dollar_amounts[7],
"GST ($)": dollar_amounts[8],
"Consumption period (days)": int(consumption_period),
"Reading date": this_reading_date,
"Reading value (kL)": this_reading_value,
"Reading type": this_reading_type,
"Water consumption (kL)": usage[0],
"Wastewater consumption (kL)": usage[1],
"Wastewater rate (%)": float(re.search(r"@(\d+.\d+)%", text).group(1)),
"Water unit rate ($/kL)": unit_rates[0],
"Wastewater unit rate ($/kL)": unit_rates[1],
}
results = []
for f in tqdm(files):
text = read_PDF(f)
results.append(parse_text(text))
df = pd.DataFrame(results)
for col in ['Invoice date', 'Due date', 'Reading date']:
df[col] = pd.to_datetime(df[col])
df.sort_values("Invoice date", inplace=True, ascending=False)
0%| | 0/64 [00:00<?, ?it/s]
In [3]:
df.style.background_gradient(cmap="RdYlGn_r").apply(lambda x: ["background: red" if v == "Estimate" else "background: green" for v in x], subset=["Reading type"])
Out[3]:
| Invoice date | Due date | Total due ($) | Opening balance ($) | Payments received ($) | Balance still owing ($) | Water consumption ($) | Wastewater consumption ($) | Wastewater fixed ($) | Balance of current charges ($) | GST ($) | Consumption period (days) | Reading date | Reading value (kL) | Reading type | Water consumption (kL) | Wastewater consumption (kL) | Wastewater rate (%) | Water unit rate ($/kL) | Wastewater unit rate ($/kL) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-09-06 00:00:00 | 2024-09-27 00:00:00 | 484.900000 | 40.240000 | -40.240000 | 0.000000 | 192.780000 | 263.240000 | 28.880000 | 484.900000 | 63.250000 | 34 | 2024-09-03 00:00:00 | 1200 | Actual | 90.000000 | 70.650000 | 78.500000 | 2.142000 | 3.726000 |
| 63 | 2024-07-31 00:00:00 | 2024-08-21 00:00:00 | 40.240000 | -2.960000 | 0.000000 | -2.960000 | 8.570000 | 11.700000 | 22.930000 | 43.200000 | 5.630000 | 27 | 2024-07-31 00:00:00 | 1110 | Estimate | 4.000000 | 3.140000 | 78.500000 | 2.142000 | 3.726000 |
| 11 | 2024-07-05 00:00:00 | 2024-07-26 00:00:00 | 0.000000 | -54.220000 | 0.000000 | -54.220000 | 9.990000 | 13.630000 | 27.640000 | 51.260000 | 6.690000 | 35 | 2024-07-04 00:00:00 | 1106 | Actual | 5.000000 | 3.920000 | 78.500000 | 1.998000 | 3.476000 |
| 22 | 2024-05-30 00:00:00 | 2024-06-20 00:00:00 | 0.000000 | -86.570000 | 0.000000 | -86.570000 | 5.990000 | 8.200000 | 18.160000 | 32.350000 | 4.220000 | 23 | 2024-05-30 00:00:00 | 1101 | Estimate | 3.000000 | 2.360000 | 78.500000 | 1.998000 | 3.476000 |
| 33 | 2024-05-08 00:00:00 | 2024-05-29 00:00:00 | 0.000000 | 45.730000 | -45.730000 | 0.000000 | -49.950000 | -68.200000 | 31.580000 | -86.570000 | -11.290000 | 40 | 2024-05-07 00:00:00 | 1098 | Actual | 25.000000 | 19.620000 | 78.500000 | 1.998000 | 3.476000 |
| 44 | 2024-03-28 00:00:00 | 2024-04-18 00:00:00 | 45.730000 | 74.910000 | -74.910000 | 0.000000 | 11.990000 | 16.370000 | 17.370000 | 45.730000 | 5.960000 | 22 | 2024-03-28 00:00:00 | 1123 | Estimate | 6.000000 | 4.710000 | 78.500000 | 1.998000 | 3.476000 |
| 55 | 2024-03-07 00:00:00 | 2024-03-28 00:00:00 | 74.910000 | 67.790000 | -67.790000 | 0.000000 | 19.980000 | 27.290000 | 27.640000 | 74.910000 | 9.770000 | 35 | 2024-03-06 00:00:00 | 1117 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.998000 | 3.476000 |
| 60 | 2024-01-31 00:00:00 | 2024-02-21 00:00:00 | 67.790000 | 66.210000 | -66.210000 | 0.000000 | 17.980000 | 24.540000 | 25.270000 | 67.790000 | 8.840000 | 32 | 2024-01-31 00:00:00 | 1107 | Estimate | 9.000000 | 7.060000 | 78.500000 | 1.998000 | 3.476000 |
| 61 | 2024-01-03 00:00:00 | 2024-01-24 00:00:00 | 66.210000 | 59.920000 | -59.920000 | 0.000000 | 17.980000 | 24.540000 | 23.690000 | 66.210000 | 8.640000 | 30 | 2023-12-30 00:00:00 | 1098 | Estimate | 9.000000 | 7.060000 | 78.500000 | 1.998000 | 3.476000 |
| 62 | 2023-11-30 00:00:00 | 2023-12-21 00:00:00 | 59.920000 | 74.120000 | -74.120000 | 0.000000 | 15.980000 | 21.830000 | 22.110000 | 59.920000 | 7.820000 | 28 | 2023-11-30 00:00:00 | 1089 | Estimate | 8.000000 | 6.280000 | 78.500000 | 1.998000 | 3.476000 |
| 1 | 2023-11-03 00:00:00 | 2023-11-24 00:00:00 | 74.120000 | 60.710000 | -60.710000 | 0.000000 | 19.980000 | 27.290000 | 26.850000 | 74.120000 | 9.670000 | 34 | 2023-11-02 00:00:00 | 1081 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.998000 | 3.476000 |
| 2 | 2023-09-29 00:00:00 | 2023-10-20 00:00:00 | 60.710000 | 57.590000 | -57.590000 | 0.000000 | 15.980000 | 21.830000 | 22.900000 | 60.710000 | 7.920000 | 29 | 2023-09-29 00:00:00 | 1071 | Estimate | 8.000000 | 6.280000 | 78.500000 | 1.998000 | 3.476000 |
| 3 | 2023-09-01 00:00:00 | 2023-09-22 00:00:00 | 57.590000 | 70.650000 | -70.650000 | 0.000000 | 13.990000 | 19.120000 | 24.480000 | 57.590000 | 7.510000 | 31 | 2023-08-31 00:00:00 | 1063 | Actual | 7.000000 | 5.500000 | 78.500000 | 1.998000 | 3.476000 |
| 4 | 2023-07-31 00:00:00 | 2023-08-21 00:00:00 | 70.650000 | 60.530000 | -60.530000 | 0.000000 | 20.080000 | 27.420000 | 23.150000 | 70.650000 | 9.220000 | 32 | 2023-07-31 00:00:00 | 1056 | Estimate | 11.000000 | 8.640000 | 78.500000 | 1.825000 | 3.174000 |
| 5 | 2023-07-03 00:00:00 | 2023-07-24 00:00:00 | 60.530000 | 74.220000 | -74.220000 | 0.000000 | 16.420000 | 22.410000 | 21.700000 | 60.530000 | 7.900000 | 30 | 2023-06-29 00:00:00 | 1045 | Actual | 9.000000 | 7.060000 | 78.500000 | 1.825000 | 3.174000 |
| 6 | 2023-05-30 00:00:00 | 2023-06-20 00:00:00 | 74.220000 | 85.750000 | -85.750000 | 0.000000 | 21.900000 | 29.900000 | 22.420000 | 74.220000 | 9.680000 | 31 | 2023-05-30 00:00:00 | 1036 | Estimate | 12.000000 | 9.420000 | 78.500000 | 1.825000 | 3.174000 |
| 7 | 2023-05-01 00:00:00 | 2023-05-22 00:00:00 | 85.750000 | 61.250000 | -61.250000 | 0.000000 | 27.380000 | 37.390000 | 20.980000 | 85.750000 | 11.180000 | 29 | 2023-04-29 00:00:00 | 1024 | Actual | 15.000000 | 11.780000 | 78.500000 | 1.825000 | 3.174000 |
| 8 | 2023-03-31 00:00:00 | 2023-04-21 00:00:00 | 61.250000 | 37.520000 | -37.520000 | 0.000000 | 16.420000 | 22.410000 | 22.420000 | 61.250000 | 7.990000 | 31 | 2023-03-31 00:00:00 | 1009 | Estimate | 9.000000 | 7.060000 | 78.500000 | 1.825000 | 3.174000 |
| 9 | 2023-03-01 00:00:00 | 2023-03-22 00:00:00 | 37.520000 | 86.470000 | -86.470000 | 0.000000 | 7.300000 | 9.970000 | 20.250000 | 37.520000 | 4.890000 | 28 | 2023-02-28 00:00:00 | 1000 | Actual | 4.000000 | 3.140000 | 78.500000 | 1.825000 | 3.174000 |
| 10 | 2023-01-31 00:00:00 | 2023-02-21 00:00:00 | 86.470000 | 66.310000 | -66.310000 | 0.000000 | 25.550000 | 34.880000 | 26.040000 | 86.470000 | 11.280000 | 36 | 2023-01-31 00:00:00 | 996 | Estimate | 14.000000 | 10.990000 | 78.500000 | 1.825000 | 3.174000 |
| 12 | 2023-01-04 00:00:00 | 2023-01-25 00:00:00 | 66.310000 | 63.420000 | -63.420000 | 0.000000 | 20.080000 | 27.420000 | 18.810000 | 66.310000 | 8.650000 | 26 | 2022-12-26 00:00:00 | 982 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.825000 | 3.174000 |
| 13 | 2022-11-30 00:00:00 | 2022-12-21 00:00:00 | 63.420000 | 71.370000 | -71.370000 | 0.000000 | 18.250000 | 24.920000 | 20.250000 | 63.420000 | 8.270000 | 28 | 2022-11-30 00:00:00 | 971 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.825000 | 3.174000 |
| 14 | 2022-11-03 00:00:00 | 2022-11-24 00:00:00 | 71.370000 | 68.480000 | -68.480000 | 0.000000 | 20.080000 | 27.420000 | 23.870000 | 71.370000 | 9.310000 | 33 | 2022-11-02 00:00:00 | 961 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.825000 | 3.174000 |
| 15 | 2022-09-30 00:00:00 | 2022-10-21 00:00:00 | 68.480000 | 54.830000 | -54.830000 | 0.000000 | 20.080000 | 27.420000 | 20.980000 | 68.480000 | 8.930000 | 29 | 2022-09-30 00:00:00 | 950 | Estimate | 11.000000 | 8.640000 | 78.500000 | 1.825000 | 3.174000 |
| 16 | 2022-09-02 00:00:00 | 2022-09-23 00:00:00 | 54.830000 | 77.790000 | -77.790000 | 0.000000 | 12.780000 | 17.460000 | 24.590000 | 54.830000 | 7.150000 | 34 | 2022-09-01 00:00:00 | 939 | Actual | 7.000000 | 5.500000 | 78.500000 | 1.825000 | 3.174000 |
| 17 | 2022-07-29 00:00:00 | 2022-08-19 00:00:00 | 77.790000 | 116.490000 | -116.490000 | 0.000000 | 25.550000 | 34.880000 | 17.360000 | 77.790000 | 10.150000 | 24 | 2022-07-29 00:00:00 | 932 | Estimate | 14.000000 | 10.990000 | 78.500000 | 1.825000 | 3.174000 |
| 18 | 2022-07-06 00:00:00 | 2022-07-27 00:00:00 | 116.490000 | 70.700000 | -70.700000 | 0.000000 | 39.240000 | 53.570000 | 23.680000 | 116.490000 | 15.190000 | 35 | 2022-07-05 00:00:00 | 918 | Actual | 23.000000 | 18.060000 | 78.500000 | 1.706000 | 2.966000 |
| 20 | 2022-05-31 00:00:00 | 2022-06-21 00:00:00 | 70.700000 | 99.670000 | -99.670000 | 0.000000 | 22.180000 | 30.250000 | 18.270000 | 70.700000 | 9.220000 | 27 | 2022-05-31 00:00:00 | 895 | Estimate | 13.000000 | 10.200000 | 78.500000 | 1.706000 | 2.966000 |
| 19 | 2022-05-05 00:00:00 | 2022-05-26 00:00:00 | 99.670000 | 58.610000 | -58.610000 | 0.000000 | 32.410000 | 44.250000 | 23.010000 | 99.670000 | 13.000000 | 34 | 2022-05-04 00:00:00 | 882 | Actual | 19.000000 | 14.920000 | 78.500000 | 1.706000 | 2.966000 |
| 21 | 2022-03-31 00:00:00 | 2022-04-21 00:00:00 | 58.610000 | 80.160000 | -80.160000 | 0.000000 | 17.060000 | 23.280000 | 18.270000 | 58.610000 | 7.640000 | 27 | 2022-03-31 00:00:00 | 863 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.706000 | 2.966000 |
| 23 | 2022-03-07 00:00:00 | 2022-03-28 00:00:00 | 80.160000 | 42.460000 | -42.460000 | 0.000000 | 23.880000 | 32.600000 | 23.680000 | 80.160000 | 10.460000 | 35 | 2022-03-04 00:00:00 | 853 | Actual | 14.000000 | 10.990000 | 78.500000 | 1.706000 | 2.966000 |
| 24 | 2022-01-28 00:00:00 | 2022-02-18 00:00:00 | 42.460000 | 74.800000 | -74.800000 | 0.000000 | 11.940000 | 16.310000 | 14.210000 | 42.460000 | 5.540000 | 21 | 2022-01-28 00:00:00 | 839 | Estimate | 7.000000 | 5.500000 | 78.500000 | 1.706000 | 2.966000 |
| 25 | 2022-01-10 00:00:00 | 2022-02-01 00:00:00 | 74.800000 | 63.350000 | -63.350000 | 0.000000 | 20.470000 | 27.940000 | 26.390000 | 74.800000 | 9.760000 | 39 | 2022-01-07 00:00:00 | 832 | Actual | 12.000000 | 9.420000 | 78.500000 | 1.706000 | 2.966000 |
| 26 | 2021-11-29 00:00:00 | 2021-12-20 00:00:00 | 63.350000 | 66.730000 | -66.730000 | 0.000000 | 18.770000 | 25.630000 | 18.950000 | 63.350000 | 8.260000 | 28 | 2021-11-29 00:00:00 | 820 | Estimate | 11.000000 | 8.640000 | 78.500000 | 1.706000 | 2.966000 |
| 27 | 2021-11-02 00:00:00 | 2021-11-23 00:00:00 | 66.730000 | 72.730000 | -72.730000 | 0.000000 | 18.770000 | 25.630000 | 22.330000 | 66.730000 | 8.700000 | 33 | 2021-11-01 00:00:00 | 809 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.706000 | 2.966000 |
| 28 | 2021-09-29 00:00:00 | 2021-10-20 00:00:00 | 72.730000 | 74.080000 | -74.080000 | 0.000000 | 22.180000 | 30.250000 | 20.300000 | 72.730000 | 9.490000 | 30 | 2021-09-29 00:00:00 | 798 | Estimate | 13.000000 | 10.200000 | 78.500000 | 1.706000 | 2.966000 |
| 30 | 2021-08-31 00:00:00 | 2021-09-21 00:00:00 | 74.080000 | 67.980000 | -67.980000 | 0.000000 | 22.180000 | 30.250000 | 21.650000 | 74.080000 | 9.660000 | 32 | 2021-08-30 00:00:00 | 785 | Estimate | 13.000000 | 10.200000 | 78.500000 | 1.706000 | 2.966000 |
| 29 | 2021-07-29 00:00:00 | 2021-08-19 00:00:00 | 67.980000 | 80.570000 | -80.570000 | 0.000000 | 20.720000 | 28.270000 | 18.990000 | 67.980000 | 8.870000 | 30 | 2021-07-29 00:00:00 | 772 | Estimate | 13.000000 | 10.200000 | 78.500000 | 1.594000 | 2.772000 |
| 31 | 2021-06-30 00:00:00 | 2021-07-21 00:00:00 | 80.570000 | 51.640000 | -51.640000 | 0.000000 | 25.500000 | 34.820000 | 20.250000 | 80.570000 | 10.510000 | 32 | 2021-06-29 00:00:00 | 759 | Actual | 16.000000 | 12.560000 | 78.500000 | 1.594000 | 2.772000 |
| 32 | 2021-05-28 00:00:00 | 2021-06-18 00:00:00 | 51.640000 | 57.320000 | -57.320000 | 0.000000 | 14.350000 | 19.570000 | 17.720000 | 51.640000 | 6.740000 | 28 | 2021-05-28 00:00:00 | 743 | Estimate | 9.000000 | 7.060000 | 78.500000 | 1.594000 | 2.772000 |
| 34 | 2021-05-03 00:00:00 | 2021-05-24 00:00:00 | 57.320000 | 61.730000 | -61.730000 | 0.000000 | 15.940000 | 21.760000 | 19.620000 | 57.320000 | 7.480000 | 31 | 2021-04-30 00:00:00 | 734 | Actual | 10.000000 | 7.850000 | 78.500000 | 1.594000 | 2.772000 |
| 35 | 2021-03-30 00:00:00 | 2021-04-20 00:00:00 | 61.730000 | 51.640000 | -51.640000 | 0.000000 | 17.530000 | 23.950000 | 20.250000 | 61.730000 | 8.050000 | 32 | 2021-03-30 00:00:00 | 724 | Estimate | 11.000000 | 8.640000 | 78.500000 | 1.594000 | 2.772000 |
| 36 | 2021-03-01 00:00:00 | 2021-03-22 00:00:00 | 51.640000 | 56.050000 | -56.050000 | 0.000000 | 14.350000 | 19.570000 | 17.720000 | 51.640000 | 6.740000 | 28 | 2021-02-26 00:00:00 | 713 | Actual | 9.000000 | 7.060000 | 78.500000 | 1.594000 | 2.772000 |
| 37 | 2021-01-29 00:00:00 | 2021-02-19 00:00:00 | 56.050000 | 61.100000 | -61.100000 | 0.000000 | 15.940000 | 21.760000 | 18.350000 | 56.050000 | 7.310000 | 29 | 2021-01-29 00:00:00 | 704 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.594000 | 2.772000 |
| 38 | 2021-01-05 00:00:00 | 2021-01-26 00:00:00 | 61.100000 | 61.100000 | -61.100000 | 0.000000 | 17.530000 | 23.950000 | 19.620000 | 61.100000 | 7.970000 | 31 | 2020-12-31 00:00:00 | 694 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.594000 | 2.772000 |
| 39 | 2020-11-30 00:00:00 | 2020-12-21 00:00:00 | 61.100000 | 64.230000 | -64.230000 | 0.000000 | 17.530000 | 23.950000 | 19.620000 | 61.100000 | 7.970000 | 31 | 2020-11-30 00:00:00 | 683 | Estimate | 11.000000 | 8.640000 | 78.500000 | 1.594000 | 2.772000 |
| 40 | 2020-11-02 00:00:00 | 2020-11-23 00:00:00 | 64.230000 | 58.580000 | -58.580000 | 0.000000 | 19.130000 | 26.110000 | 18.990000 | 64.230000 | 8.380000 | 30 | 2020-10-30 00:00:00 | 672 | Actual | 12.000000 | 9.420000 | 78.500000 | 1.594000 | 2.772000 |
| 42 | 2020-09-30 00:00:00 | 2020-10-21 00:00:00 | 58.580000 | 48.510000 | -48.510000 | 0.000000 | 15.940000 | 21.760000 | 20.880000 | 58.580000 | 7.640000 | 33 | 2020-09-30 00:00:00 | 660 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.594000 | 2.772000 |
| 41 | 2020-08-31 00:00:00 | 2020-09-21 00:00:00 | 48.510000 | 56.690000 | -56.690000 | 0.000000 | 12.750000 | 17.410000 | 18.350000 | 48.510000 | 6.330000 | 29 | 2020-08-28 00:00:00 | 650 | Actual | 8.000000 | 6.280000 | 78.500000 | 1.594000 | 2.772000 |
| 43 | 2020-07-30 00:00:00 | 2020-08-20 00:00:00 | 56.690000 | 49.150000 | -49.150000 | 0.000000 | 15.940000 | 21.760000 | 18.990000 | 56.690000 | 7.390000 | 30 | 2020-07-30 00:00:00 | 642 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.594000 | 2.772000 |
| 45 | 2020-07-01 00:00:00 | 2020-07-22 00:00:00 | 49.150000 | 62.010000 | -62.010000 | 0.000000 | 12.440000 | 16.980000 | 19.730000 | 49.150000 | 6.410000 | 32 | 2020-06-30 00:00:00 | 632 | Actual | 8.000000 | 6.280000 | 78.500000 | 1.555000 | 2.704000 |
| 46 | 2020-05-29 00:00:00 | 2020-06-19 00:00:00 | 62.010000 | 63.240000 | -63.240000 | 0.000000 | 18.660000 | 25.470000 | 17.880000 | 62.010000 | 8.090000 | 29 | 2020-05-29 00:00:00 | 624 | Estimate | 12.000000 | 9.420000 | 78.500000 | 1.555000 | 2.704000 |
| 47 | 2020-05-01 00:00:00 | 2020-05-22 00:00:00 | 63.240000 | 70.600000 | -70.600000 | 0.000000 | 18.660000 | 25.470000 | 19.110000 | 63.240000 | 8.250000 | 31 | 2020-04-30 00:00:00 | 612 | Actual | 12.000000 | 9.420000 | 78.500000 | 1.555000 | 2.704000 |
| 48 | 2020-03-30 00:00:00 | 2020-04-20 00:00:00 | 70.600000 | 57.720000 | -57.720000 | 0.000000 | 21.770000 | 29.720000 | 19.110000 | 70.600000 | 9.210000 | 31 | 2020-03-30 00:00:00 | 600 | Estimate | 14.000000 | 10.990000 | 78.500000 | 1.555000 | 2.704000 |
| 49 | 2020-03-02 00:00:00 | 2020-03-23 00:00:00 | 57.720000 | 74.280000 | -74.280000 | 0.000000 | 17.100000 | 23.360000 | 17.260000 | 57.720000 | 7.530000 | 28 | 2020-02-28 00:00:00 | 586 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.555000 | 2.704000 |
| 50 | 2020-01-31 00:00:00 | 2020-02-21 00:00:00 | 74.280000 | 93.280000 | -93.280000 | 0.000000 | 23.320000 | 31.850000 | 19.110000 | 74.280000 | 9.690000 | 31 | 2020-01-31 00:00:00 | 575 | Estimate | 15.000000 | 11.780000 | 78.500000 | 1.555000 | 2.704000 |
| 51 | 2020-01-03 00:00:00 | 2020-01-24 00:00:00 | 93.280000 | 54.660000 | -54.660000 | 0.000000 | 31.100000 | 42.450000 | 19.730000 | 93.280000 | 12.170000 | 32 | 2019-12-31 00:00:00 | 560 | Actual | 20.000000 | 15.700000 | 78.500000 | 1.555000 | 2.704000 |
| 52 | 2019-12-05 00:00:00 | 2019-12-30 00:00:00 | 54.660000 | 0.000000 | 0.000000 | 0.000000 | 15.550000 | 21.230000 | 17.880000 | 54.660000 | 7.130000 | 29 | 2019-11-29 00:00:00 | 540 | Estimate | 10.000000 | 7.850000 | 78.500000 | 1.555000 | 2.704000 |
| 53 | 2019-11-05 00:00:00 | 2019-11-26 00:00:00 | 52.230000 | 66.940000 | -66.940000 | 0.000000 | 14.000000 | 19.120000 | 19.110000 | 52.230000 | 6.810000 | 31 | 2019-10-31 00:00:00 | 530 | Actual | 9.000000 | 7.070000 | 78.500000 | 1.555000 | 2.704000 |
| 54 | 2019-10-02 00:00:00 | 2019-10-23 00:00:00 | 66.940000 | 58.960000 | -58.960000 | 0.000000 | 20.220000 | 27.610000 | 19.110000 | 66.940000 | 8.730000 | 31 | 2019-09-30 00:00:00 | 521 | Estimate | 13.000000 | 10.210000 | 78.500000 | 1.555000 | 2.704000 |
| 56 | 2019-09-04 00:00:00 | 2019-09-25 00:00:00 | 58.960000 | 73.670000 | -73.670000 | 0.000000 | 17.110000 | 23.360000 | 18.490000 | 58.960000 | 7.690000 | 30 | 2019-08-30 00:00:00 | 508 | Actual | 11.000000 | 8.640000 | 78.500000 | 1.555000 | 2.704000 |
| 57 | 2019-08-02 00:00:00 | 2019-08-23 00:00:00 | 73.670000 | 79.330000 | -79.330000 | 0.000000 | 23.330000 | 31.850000 | 18.490000 | 73.670000 | 9.610000 | 30 | 2019-07-31 00:00:00 | 497 | Estimate | 15.000000 | 11.780000 | 78.500000 | 1.555000 | 2.704000 |
| 58 | 2019-07-04 00:00:00 | 2019-07-25 00:00:00 | 79.330000 | 67.930000 | -67.930000 | 0.000000 | 25.810000 | 34.980000 | 18.540000 | 79.330000 | 10.350000 | 31 | 2019-07-01 00:00:00 | 482 | Actual | 16.450000 | 0.550000 | 78.500000 | 1.517000 | 2.618000 |
| 59 | 2019-06-05 00:00:00 | 2019-06-26 00:00:00 | 67.930000 | 76.860000 | -76.860000 | 0.000000 | 21.240000 | 28.770000 | 17.920000 | 67.930000 | 8.860000 | 30 | 2019-05-31 00:00:00 | 465 | Estimate | 14.000000 | 10.990000 | 78.500000 | 1.517000 | 2.618000 |
In [4]:
df.describe()
Out[4]:
| Invoice date | Due date | Total due ($) | Opening balance ($) | Payments received ($) | Balance still owing ($) | Water consumption ($) | Wastewater consumption ($) | Wastewater fixed ($) | Balance of current charges ($) | GST ($) | Consumption period (days) | Reading date | Reading value (kL) | Water consumption (kL) | Wastewater consumption (kL) | Wastewater rate (%) | Water unit rate ($/kL) | Wastewater unit rate ($/kL) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| count | 64 | 64 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64 | 64.000000 | 64.000000 | 64.000000 | 64.0 | 64.000000 | 64.000000 |
| mean | 2022-01-15 21:22:30 | 2022-02-05 23:15:00 | 69.244219 | 59.806406 | -62.052500 | -2.246094 | 20.400781 | 27.845312 | 20.998125 | 69.244219 | 9.032187 | 30.500000 | 2022-01-14 10:07:30 | 835.015625 | 12.475781 | 9.601094 | 78.5 | 1.737016 | 3.020250 |
| min | 2019-06-05 00:00:00 | 2019-06-26 00:00:00 | 0.000000 | -86.570000 | -116.490000 | -86.570000 | -49.950000 | -68.200000 | 14.210000 | -86.570000 | -11.290000 | 21.000000 | 2019-05-31 00:00:00 | 465.000000 | 3.000000 | 0.550000 | 78.5 | 1.517000 | 2.618000 |
| 25% | 2020-09-22 12:00:00 | 2020-10-13 12:00:00 | 57.162500 | 57.162500 | -73.772500 | 0.000000 | 15.940000 | 21.760000 | 18.527500 | 57.162500 | 7.457500 | 29.000000 | 2020-09-21 18:00:00 | 657.500000 | 9.000000 | 7.060000 | 78.5 | 1.594000 | 2.772000 |
| 50% | 2022-01-19 00:00:00 | 2022-02-09 12:00:00 | 63.385000 | 63.385000 | -63.385000 | 0.000000 | 18.455000 | 25.195000 | 19.990000 | 63.385000 | 8.265000 | 31.000000 | 2022-01-17 12:00:00 | 835.500000 | 11.000000 | 8.640000 | 78.5 | 1.706000 | 2.966000 |
| 75% | 2023-05-08 06:00:00 | 2023-05-29 06:00:00 | 73.772500 | 73.772500 | -57.162500 | 0.000000 | 21.802500 | 29.765000 | 22.950000 | 73.772500 | 9.622500 | 32.000000 | 2023-05-06 18:00:00 | 1027.000000 | 13.000000 | 10.200000 | 78.5 | 1.825000 | 3.174000 |
| max | 2024-09-06 00:00:00 | 2024-09-27 00:00:00 | 484.900000 | 116.490000 | 0.000000 | 0.000000 | 192.780000 | 263.240000 | 31.580000 | 484.900000 | 63.250000 | 40.000000 | 2024-09-03 00:00:00 | 1200.000000 | 90.000000 | 70.650000 | 78.5 | 2.142000 | 3.726000 |
| std | NaN | NaN | 56.223242 | 29.704873 | 20.942004 | 12.674220 | 24.183486 | 33.020630 | 3.312389 | 57.812202 | 7.540910 | 3.427248 | NaN | 207.889355 | 10.618429 | 8.404957 | 0.0 | 0.175876 | 0.307204 |
In [5]:
df.groupby(df["Invoice date"].dt.year)["Balance of current charges ($)"].sum()
Out[5]:
Invoice date 2019 453.72 2020 759.39 2021 764.92 2022 878.78 2023 795.04 2024 779.78 Name: Balance of current charges ($), dtype: float64
In [6]:
px.scatter(df, x="Reading date", y="Reading value (kL)", color="Reading type", title="Reading values (kL)", trendline="ols")
In [7]:
px.bar(df, x="Reading date", y="Water consumption (kL)")
In [8]:
df["Average daily usage (L)"] = df["Water consumption (kL)"] * 1000 / df["Consumption period (days)"]
px.bar(df, x="Reading date", y="Average daily usage (L)")
In [9]:
px.bar(df, x="Reading date", y="Balance of current charges ($)")
In [10]:
px.line(df, x="Reading date", y=["Water unit rate ($/kL)", "Wastewater unit rate ($/kL)"], title="Unit rates ($/kL)")